/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package info.toegepaste.www.dataaccess;

import info.toegepaste.www.beans.Alumnus;
import info.toegepaste.www.beans.Richting;
import info.toegepaste.www.beans.Werkstatus;
import java.sql.*;
import java.math.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Alumnus Data Access Object (DAO). This class contains all database handling
 * that is needed to permanently store and retrieve Alumnus object instances.
 */
/**
 * This sourcecode has been generated by FREE DaoGen generator version 2.4.1.
 * The usage of generated code is restricted to OpenSource software projects
 * only. DaoGen is available in http://titaniclinux.net/daogen/ It has been
 * programmed by Tuomo Lukka, Tuomo.Lukka@iki.fi
 *
 * DaoGen license: The following DaoGen generated source code is licensed under
 * the terms of GNU GPL license. The full text for license is available in GNU
 * project's pages: http://www.gnu.org/copyleft/gpl.html
 *
 * If you wish to use the DaoGen generator to produce code for closed-source
 * commercial applications, you must pay the lisence fee. The price is 5 USD or
 * 5 Eur for each database table, you are generating code for. (That includes
 * unlimited amount of iterations with all supported languages for each database
 * table you are paying for.) Send mail to "Tuomo.Lukka@iki.fi" for more
 * information. Thank you!
 */
public class DAAlumnus {

    private Connection connection = null;

    public DAAlumnus(String url, String login, String password) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        connection = DriverManager.getConnection(url, login, password);
    }

    public DAAlumnus(Connection conn) {
        connection = conn;
    }

    public void close() throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }

    /**
     * createValueObject-method. This method is used when the Dao class needs to
     * create new value object instance. The reason why this method exists is
     * that sometimes the programmer may want to extend also the valueObject and
     * then this method can be overrided to return extended valueObject. NOTE:
     * If you extend the valueObject class, make sure to override the clone()
     * method in it!
     */
    public Alumnus createValueObject() {
        return new Alumnus();
    }

    /**
     * getObject-method. This will create and load valueObject contents from
     * database using given Primary-Key as identifier. This method is just a
     * convenience method for the real load-method which accepts the valueObject
     * as a parameter. Returned valueObject will be created using the
     * createValueObject() method.
     */
    public Alumnus getAlumnus(int alumnusID) throws SQLException {

        Alumnus valueObject = createValueObject();
        valueObject.setAlumnusID(alumnusID);
        load(valueObject);
        return valueObject;
    }

    /**
     * load-method. This will load valueObject contents from database using
     * Primary-Key as identifier. Upper layer should use this so that
     * valueObject instance is created and only primary-key should be specified.
     * Then call this method to complete other persistent information. This
     * method will overwrite all other fields except primary-key and possible
     * runtime variables. If load can not find matching row, NotFoundException
     * will be thrown.
     *
     * @param connection This method requires working database connection.
     * @param valueObject This parameter contains the class instance to be
     * loaded. Primary-key field must be set for this to work properly.
     */
    public void load(Alumnus valueObject) throws SQLException {

        String sql = "SELECT * FROM alumnus WHERE (alumnusID = ? ) ";
        PreparedStatement statement = null;

        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, valueObject.getAlumnusID());

            singleQuery(statement, valueObject);

        } catch (SQLException ex) {
            Logger.getLogger(DAAlumnus.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    /**
     * LoadAll-method. This will read all contents from database table and build
     * a List containing valueObjects. Please note, that this method will
     * consume huge amounts of resources if table has lot's of rows. This should
     * only be used when target tables have only small amounts of data.
     *
     * @param connection This method requires working database connection.
     */
    public List getAlleAlumni() throws SQLException {

        String sql = "SELECT * FROM alumnus ORDER BY alumnusID ASC ";
        List searchResults = listQuery(connection.prepareStatement(sql));

        return searchResults;
    }

    public int countAlumni() throws SQLException {
        String sql = "SELECT COUNT(*) from alumnus";
        int count = statistiekQuery(connection.prepareStatement(sql));
        return count;
    }

    public int countWerkendeAlumni() throws SQLException {
        String sql = "SELECT COUNT(*) from alumnus WHERE functieID IS NOT NULL";
        int count = statistiekQuery(connection.prepareStatement(sql));
        return count;
    }

    public int countWerklozeAlumni() throws SQLException {
        String sql = "SELECT COUNT(*) from alumnus WHERE functieID IS NULL";
        int count = statistiekQuery(connection.prepareStatement(sql));
        return count;
    }

    public int countApplicatieAlumni() throws SQLException {
        String sql = "SELECT COUNT(*) from alumnus WHERE richtingID = 1";
        int count = statistiekQuery(connection.prepareStatement(sql));
        return count;
    }

    public int countBusinessAlumni() throws SQLException {
        String sql = "SELECT COUNT(*) from alumnus WHERE richtingID = 3";
        int count = statistiekQuery(connection.prepareStatement(sql));
        return count;
    }

    public int countNetwerkAlumni() throws SQLException {
        String sql = "SELECT COUNT(*) from alumnus WHERE richtingID = 2";
        int count = statistiekQuery(connection.prepareStatement(sql));
        return count;
    }

    public List zoekAlumniOpNaam(String naam) throws SQLException {
        String sql = "SELECT * FROM alumnus WHERE CONCAT(voornaam,' ',achternaam) LIKE ? OR CONCAT(achternaam,' ',voornaam) LIKE ? ORDER BY achternaam";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, "%" + naam + "%");
        statement.setString(2, "%" + naam + "%");


        return listQuery(statement);
    }

    public List zoekAlumniOpAfstudeerjaar(String jaar) throws SQLException {
        String sql = "select * from alumnus WHERE afstudeerjaar = ? ORDER BY achternaam";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, jaar);
        return listQuery(statement);
    }

    public List zoekAlumniOpWerkstatus(Werkstatus werkstatus) throws SQLException {
        String sql = "select * from alumnus WHERE werkstatusID = ? ORDER BY achternaam";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, werkstatus.getWerkstatusID());
        return listQuery(statement);
    }

    public List zoekAlumniOpRichting(Richting richting) throws SQLException {
        String sql = "select * from alumnus WHERE richtingID = ? ORDER BY achternaam";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, richting.getRichtingID());
        return listQuery(statement);
    }

    /**
     * create-method. This will create new row in database according to supplied
     * valueObject contents. Make sure that values for all NOT NULL columns are
     * correctly specified. Also, if this table does not use automatic
     * surrogate-keys the primary-key must be specified. After INSERT command
     * this method will read the generated primary-key back to valueObject if
     * automatic surrogate-keys were used.
     *
     * @param connection This method requires working database connection.
     * @param valueObject This parameter contains the class instance to be
     * created. If automatic surrogate-keys are not used the Primary-key field
     * must be set for this to work properly.
     */
    public synchronized int create(Alumnus valueObject) throws SQLException {

        String sql = "";
        PreparedStatement statement = null;
        ResultSet result = null;
        int rowcount;
        try {
            sql = "INSERT INTO alumnus ( alumnusID, voornaam, achternaam, "
                    + "emailprive, afstudeerjaar, functieID, "
                    + "richtingID, bedrijfID, werkstatusID, "
                    + "sinds) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            statement.setInt(1, valueObject.getAlumnusID());
            statement.setString(2, valueObject.getVoornaam());
            statement.setString(3, valueObject.getAchternaam());
            statement.setString(4, valueObject.getEmailprive());
            statement.setString(5, valueObject.getAfstudeerjaar());
            statement.setInt(6, valueObject.getFunctie().getFunctieID());
            statement.setInt(7, valueObject.getRichting().getRichtingID());
            statement.setInt(8, valueObject.getBedrijf().getBedrijfID());
            statement.setInt(9, valueObject.getWerkstatus().getWerkstatusID());
            statement.setDate(10, valueObject.getSinds());

            rowcount = databaseUpdate(statement);


        } finally {
            if (statement != null) {
                statement.close();
            }
        }
        return rowcount;


    }

    /**
     * update-method. This method will update the current state of valueObject
     * to database. Save can not be used to create new instances in database, so
     * upper layer must make sure that the primary-key is correctly specified.
     * Primary-key will indicate which instance is going to be updated in
     * database. If update can not find matching row, NotFoundException will be
     * thrown.
     *
     * @param connection This method requires working database connection.
     * @param valueObject This parameter contains the class instance to be
     * saved. Primary-key field must be set for this to work properly.
     */
    public void update(Alumnus valueObject)
            throws SQLException {

        String sql = "UPDATE alumnus SET voornaam = ?, achternaam = ?, emailprive = ?, "
                + "afstudeerjaar = ?, functieID = ?, richtingID = ?, "
                + "bedrijfID = ?, werkstatusID = ?, sinds = ? WHERE (alumnusID = ? ) ";
        PreparedStatement statement = null;

        try {
            statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, valueObject.getVoornaam());
            statement.setString(2, valueObject.getAchternaam());
            statement.setString(3, valueObject.getEmailprive());
            statement.setString(4, valueObject.getAfstudeerjaar());
            statement.setInt(5, valueObject.getFunctie().getFunctieID());
            statement.setInt(6, valueObject.getRichting().getRichtingID());
            statement.setInt(7, valueObject.getBedrijf().getBedrijfID());
            statement.setInt(8, valueObject.getWerkstatus().getWerkstatusID());
            statement.setDate(9, valueObject.getSinds());

            statement.setInt(10, valueObject.getAlumnusID());

            int rowcount = databaseUpdate(statement);
            if (rowcount == 0) {
                System.out.println("Object could not be saved! (PrimaryKey not found)");

            }
            if (rowcount > 1) {
                //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
                throw new SQLException("PrimaryKey Error when updating DB! (Many objects were affected!)");
            }
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    /**
     * delete-method. This method will remove the information from database as
     * identified by by primary-key in supplied valueObject. Once valueObject
     * has been deleted it can not be restored by calling update. Restoring can
     * only be done using create method but if database is using automatic
     * surrogate-keys, the resulting object will have different primary-key than
     * what it was in the deleted object. If delete can not find matching row,
     * NotFoundException will be thrown.
     *
     * @param connection This method requires working database connection.
     * @param valueObject This parameter contains the class instance to be
     * deleted. Primary-key field must be set for this to work properly.
     */
    public void delete(Alumnus valueObject)
            throws SQLException {

        String sql = "DELETE FROM alumnus WHERE (alumnusID = ? ) ";
        PreparedStatement statement = null;

        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, valueObject.getAlumnusID());

            int rowcount = databaseUpdate(statement);
            if (rowcount == 0) {
                System.out.println("Object could not be deleted (PrimaryKey not found)");

            }
            if (rowcount > 1) {
                //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
                throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
            }
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }
    public void deleteAlumnus(int alumnusid) {
        String sql = "DELETE FROM alumnus WHERE alumnusID = ?";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, alumnusid);
            statement.executeUpdate();
        } catch (SQLException e) {
            
        }
    }

    /**
     * deleteAll-method. This method will remove all information from the table
     * that matches this Dao and ValueObject couple. This should be the most
     * efficient way to clear table. Once deleteAll has been called, no
     * valueObject that has been created before can be restored by calling
     * update. Restoring can only be done using create method but if database is
     * using automatic surrogate-keys, the resulting object will have different
     * primary-key than what it was in the deleted object. (Note, the
     * implementation of this method should be different with different DB
     * backends.)
     *
     * @param connection This method requires working database connection.
     */
    public void deleteAll() throws SQLException {

        String sql = "DELETE FROM alumnus";
        PreparedStatement statement = null;

        try {
            statement = connection.prepareStatement(sql);
            int rowcount = databaseUpdate(statement);
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

    /**
     * coutAll-method. This method will return the number of all rows from table
     * that matches this Dao. The implementation will simply execute "select
     * count(primarykey) from table". If table is empty, the return value is 0.
     * This method should be used before calling getAlleAlumni, to make sure
     * table has not too many rows.
     *
     * @param connection This method requires working database connection.
     */
    public int countAll() throws SQLException {

        String sql = "SELECT count(*) FROM alumnus";
        PreparedStatement statement = null;
        ResultSet result = null;
        int allRows = 0;

        try {
            statement = connection.prepareStatement(sql);
            result = statement.executeQuery();

            if (result.next()) {
                allRows = result.getInt(1);
            }
        } finally {
            if (result != null) {
                result.close();
            }
            if (statement != null) {
                statement.close();
            }
        }
        return allRows;
    }

    /**
     * searchMatching-Method. This method provides searching capability to get
     * matching valueObjects from database. It works by searching all objects
     * that match permanent instance variables of given object. Upper layer
     * should use this by setting some parameters in valueObject and then call
     * searchMatching. The result will be 0-N objects in a List, all matching
     * those criteria you specified. Those instance-variables that have NULL
     * values are excluded in search-criteria.
     *
     * @param connection This method requires working database connection.
     * @param valueObject This parameter contains the class instance where
     * search will be based. Primary-key field should not be set.
     */
    public List searchMatching(Alumnus valueObject) throws SQLException {

        List searchResults;

        boolean first = true;
        StringBuffer sql = new StringBuffer("SELECT * FROM alumnus WHERE 1=1 ");

        if (valueObject.getAlumnusID() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND alumnusID = ").append(valueObject.getAlumnusID()).append(" ");
        }

        if (valueObject.getVoornaam() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND voornaam LIKE '").append(valueObject.getVoornaam()).append("%' ");
        }

        if (valueObject.getAchternaam() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND achternaam LIKE '").append(valueObject.getAchternaam()).append("%' ");
        }

        if (valueObject.getEmailprive() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND emailprive LIKE '").append(valueObject.getEmailprive()).append("%' ");
        }

        if (valueObject.getAfstudeerjaar() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND afstudeerjaar LIKE '").append(valueObject.getAfstudeerjaar()).append("%' ");
        }

        if (valueObject.getFunctie().getFunctieID() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND functieID = ").append(valueObject.getFunctie().getFunctieID()).append(" ");
        }

        if (valueObject.getRichting().getRichtingID() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND richtingID = ").append(valueObject.getRichting().getRichtingID()).append(" ");
        }

        if (valueObject.getBedrijf().getBedrijfID() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND bedrijfID = ").append(valueObject.getBedrijf().getBedrijfID()).append(" ");
        }

        if (valueObject.getWerkstatus().getWerkstatusID() != 0) {
            if (first) {
                first = false;
            }
            sql.append("AND werkstatusID = ").append(valueObject.getWerkstatus().getWerkstatusID()).append(" ");
        }

        if (valueObject.getSinds() != null) {
            if (first) {
                first = false;
            }
            sql.append("AND sinds LIKE '").append(valueObject.getSinds()).append("%' ");
        }


        sql.append("ORDER BY alumnusID ASC ");

        // Prevent accidential full table results.
        // Use getAlleAlumni if all rows must be returned.
        if (first) {
            searchResults = new ArrayList();
        } else {
            searchResults = listQuery(connection.prepareStatement(sql.toString()));
        }

        return searchResults;
    }

    /**
     * getDaogenVersion will return information about generator which created
     * these sources.
     */
    public String getDaogenVersion() {
        return "DaoGen version 2.4.1";
    }

    /**
     * databaseUpdate-method. This method is a helper method for internal use.
     * It will execute all database handling that will change the information in
     * tables. SELECT queries will not be executed here however. The return
     * value indicates how many rows were affected. This method will also make
     * sure that if cache is used, it will reset when data changes.
     *
     * @param connection This method requires working database connection.
     * @param statement This parameter contains the SQL statement to be excuted.
     */
    protected int databaseUpdate(PreparedStatement statement) throws SQLException {

        statement.executeUpdate();
        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next()) {
            return rs.getInt(1);
        } else {
            return 0;
        }


    }

    /**
     * databaseQuery-method. This method is a helper method for internal use. It
     * will execute all database queries that will return only one row. The
     * resultset will be converted to valueObject. If no rows were found,
     * NotFoundException will be thrown.
     *
     * @param connection This method requires working database connection.
     * @param statement This parameter contains the SQL statement to be excuted.
     * @param valueObject Class-instance where resulting data will be stored.
     */
    protected void singleQuery(PreparedStatement statement, Alumnus valueObject)
            throws SQLException {
        DAFunctie dafunctie = new DAFunctie(connection);
        DARichting darichting = new DARichting(connection);
        DABedrijf dabedrijf = new DABedrijf(connection);
        DAWerkstatus dawerkstatus = new DAWerkstatus(connection);
        ResultSet result = null;

        try {
            result = statement.executeQuery();

            if (result.next()) {

                valueObject.setAlumnusID(result.getInt("alumnusID"));
                valueObject.setVoornaam(result.getString("voornaam"));
                valueObject.setAchternaam(result.getString("achternaam"));
                valueObject.setEmailprive(result.getString("emailprive"));
                valueObject.setAfstudeerjaar(result.getString("afstudeerjaar"));
                valueObject.setFunctie(dafunctie.getFunctie(result.getInt("functieID")));
                valueObject.setRichting(darichting.getRichting(result.getInt("richtingID")));
                valueObject.setBedrijf(dabedrijf.getBedrijf(result.getInt("bedrijfID")));
                valueObject.setWerkstatus(dawerkstatus.getWerkstatus(result.getInt("werkstatusID")));
                valueObject.setSinds(result.getDate("sinds"));

            } else {
                //System.out.println("Alumnus Object Not Found!");
            }
        } finally {
            if (result != null) {
                result.close();
            }
            if (statement != null) {
                statement.close();
            }
        }
    }

    /**
     * databaseQuery-method. This method is a helper method for internal use. It
     * will execute all database queries that will return multiple rows. The
     * resultset will be converted to the List of valueObjects. If no rows were
     * found, an empty List will be returned.
     *
     * @param connection This method requires working database connection.
     * @param statement This parameter contains the SQL statement to be excuted.
     */
    protected int statistiekQuery(PreparedStatement statement) throws SQLException {
        int aantal = 0;
        ResultSet result = null;

        try {
            result = statement.executeQuery();

            if (result.next()) {

                aantal = result.getInt(1);

            } else {
                //System.out.println("Alumnus Object Not Found!");
            }
        } finally {
            if (result != null) {
                result.close();
            }
            if (statement != null) {
                statement.close();
            }
        }
        return aantal;
    }

    protected List listQuery(PreparedStatement statement) throws SQLException {
        DAFunctie dafunctie = new DAFunctie(connection);
        DARichting darichting = new DARichting(connection);
        DABedrijf dabedrijf = new DABedrijf(connection);
        DAWerkstatus dawerkstatus = new DAWerkstatus(connection);
        ArrayList searchResults = new ArrayList();
        ResultSet result = null;

        try {
            result = statement.executeQuery();

            while (result.next()) {
                Alumnus temp = createValueObject();

                temp.setAlumnusID(result.getInt("alumnusID"));
                temp.setVoornaam(result.getString("voornaam"));
                temp.setAchternaam(result.getString("achternaam"));
                temp.setEmailprive(result.getString("emailprive"));
                temp.setAfstudeerjaar(result.getString("afstudeerjaar"));
                temp.setFunctie(dafunctie.getFunctie(result.getInt("functieID")));
                temp.setRichting(darichting.getRichting(result.getInt("richtingID")));
                temp.setBedrijf(dabedrijf.getBedrijf(result.getInt("bedrijfID")));
                temp.setWerkstatus(dawerkstatus.getWerkstatus(result.getInt("werkstatusID")));
                temp.setSinds(result.getDate("sinds"));

                searchResults.add(temp);
            }

        } finally {
            if (result != null) {
                result.close();
            }
            if (statement != null) {
                statement.close();
            }
        }

        return (List) searchResults;
    }
}
